package com.cchr.acms.service;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.cchr.acms.model.AbusedPerson;
import com.cchr.acms.model.Attorney;
import com.cchr.acms.model.CaseFollowUp;
import com.cchr.acms.model.CaseInfo;
import com.cchr.acms.model.DoctorInfo;
import com.cchr.acms.model.Drug;
import com.cchr.acms.model.FacilityInfo;
import com.cchr.acms.model.History;
import com.cchr.acms.model.ReportingPerson;

@Service
public class ImportDataService {

	@Autowired
	private CaseInfoService caseInfoService;
	@Autowired
	private HistoryService historyService;
	@Autowired
	private AbusedPersonService abusedPersonService;
	@Autowired
	private ReportingPersonService reportingPersonService;
	@Autowired
	private FacilityInfoService facilityInfoService;
	@Autowired
	private DoctorInfoService doctorInfoService;
	@Autowired
	private AttorneyService attorneyService;
	@Autowired
	private CaseFollowUpService caseFollowUpService;
	@Autowired
	private DrugService drugService;

	private static final Logger exceptionLog = LoggerFactory.getLogger("exceptionLog");
	public static int caseId;

	public String getDrugsIds(String drugNames) {

		Map<String, Integer> m = new HashMap<String, Integer>();
		List<Drug> drugs = drugService.getAllDrug();

		for (Drug drg : drugs) {
			m.put(drg.getName().toString().trim().toUpperCase(), drg.getId());
		}
		drugNames = drugNames.replaceAll(";", ",");
		String[] names = drugNames.split(",");
		String s = "";
		for (String n : names) {
			if (null != n && n.length() > 1) {
				try {
					s = s + m.get(n.toString().trim().toUpperCase()) + ",";
				} catch (Exception e) {

					exceptionLog.error("drug is not in list -->" + n);
				}
			}
		}
		if (s.length() > 1) {
			s = s.substring(0, s.length() - 1); // 去掉末尾,
		} else {
			s = s.substring(0, s.length());
		}

		return s;
	}

	public void saveData() throws Exception {

		// each record for CaseInfo, ReportingPerson, AbusedPerson,
		// CaseFollowUp, Attorney, History
		
		

		//Workbook wb = WorkbookFactory.create(new FileInputStream("C:\\Users\\user\\git\\qwwy-yl\\src\\main\\resources\\data\\all6.xlsx"));
		//Workbook wb = WorkbookFactory.create(new FileInputStream("C:\\Users\\user\\git\\qwwy-yl\\src\\main\\resources\\data\\debug_data.xlsx"));
		

		//InputStream input = this.getClass().getClassLoader().getResourceAsStream("data/debug_data.xlsx"); // 建立输入流
		File file =  new File(this.getClass().getClassLoader().getResource("data/test.txt").getFile());
		InputStreamReader read = new InputStreamReader(new FileInputStream(file));
		String fileName =  new BufferedReader(read).readLine().trim();
		System.out.println("load file name: "+fileName);
		InputStream input = this.getClass().getClassLoader().getResourceAsStream(fileName); // 建立输入流
		read.close();
		
		//InputStream input = this.getClass().getClassLoader().getResourceAsStream("data/all6.xlsx"); // 建立输入流

		Workbook wb = new XSSFWorkbook(input);
		Sheet xssfSheet;
		
		Row row;
		Cell cell;
		xssfSheet = wb.getSheetAt(0);

		int rowstart = 1;
		int rowEnd = xssfSheet.getLastRowNum();

		int cellStart = 0; // row.getFirstCellNum();
		int cellEnd = 95; // row.getLastCellNum();

		for (int i = rowstart; i <= rowEnd; i++) {
			row = xssfSheet.getRow(i);

			// prepare object
			CaseInfo caseInfo = new CaseInfo();
			AbusedPerson abusedPerson = new AbusedPerson();
			ReportingPerson reportedPersonBy = new ReportingPerson();
			CaseFollowUp caseFollowUp = new CaseFollowUp();
			FacilityInfo facilityInfo = new FacilityInfo();
			History history = new History();
			DoctorInfo doctorInfo = new DoctorInfo();
			Attorney attorney = new Attorney();
			StringBuilder abuseTypeIDs = new StringBuilder();
			String summary= null;
			String notes= null;
	

			for (int k = cellStart; k <= cellEnd; k++) {
				cell = row.getCell(k);

				switch (k) {
				case 0: // AbusedPerson.lastName
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setLastName(cell.getStringCellValue());
					} else {
						abusedPerson.setLastName("");
					}
					break;
				case 1: // AbusedPerson.firstName
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setFirstName(cell.getStringCellValue());
					} else {
						abusedPerson.setFirstName("");
					}
					break;
				case 2: // AbusedPerson.middleName
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setMiddleName(cell.getStringCellValue());
					} else {
						abusedPerson.setMiddleName("");
					}
					break;
				case 3: // AbusedPerson.telephone
					if (null != cell) {
						if (cell.getCellType() == 0) {
							abusedPerson.setTelephone(String.valueOf(cell.getNumericCellValue()));
						}
						if (cell.getCellType() == 1) {
							abusedPerson.setTelephone(cell.getStringCellValue());
						}
					} else {
						abusedPerson.setTelephone("");
					}
					break;
				case 4: // AbusedPerson.email
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setEmail(cell.getStringCellValue());
					} else {
						abusedPerson.setEmail(null);
					}
					break;
				case 5: // AbusedPerson.streetAddress
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setStreetAddress(cell.getStringCellValue());
					} else {
						abusedPerson.setStreetAddress("");
					}
					break;
				case 6:// AbusedPerson.city
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setCity(cell.getStringCellValue());
					} else {
						abusedPerson.setCity("");
					}
					break;
				case 7: // AbusedPerson.state
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setState(cell.getStringCellValue());
					} else {
						abusedPerson.setState("");
					}
					break;
				case 8: // AbusedPerson.zip
					if (null != cell) {
						if (0 == cell.getCellType()) {
							String zip = String.valueOf(cell.getNumericCellValue());
							abusedPerson.setZip(zip.substring(0, zip.indexOf(".")));
						}
						if (1 == cell.getCellType()) {
							abusedPerson.setZip(cell.getStringCellValue());
						}

					} else {
						abusedPerson.setZip("");
					}
					break;
				case 9: // AbusedPerson.country
					if (null != cell && 1 == cell.getCellType()) {
						abusedPerson.setCountry(cell.getStringCellValue());
					} else {
						abusedPerson.setCountry("");
					}
					break;
				case 10: // AbusedPerson.birthDay
					if (null != cell && 0 == cell.getCellType()) {
						try {

							abusedPerson.setBirthDay(cell.getDateCellValue());

						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 10, AbusedPerson.birthDay is not Date foramt-->" + e.toString());
						}
					} 
					break;
				case 11: // AbusedPerson.age
					if (null != cell && 0 == cell.getCellType()) {
						try {
							abusedPerson.setAge((int) cell.getNumericCellValue());
						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 11, AbusedPerson.age is not numeric foramt-->" + e.toString());
						}
					} else {
						abusedPerson.setAge(0);
					}
					break;
				case 12:// ReportingPerson.lastName
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setLastName(cell.getStringCellValue());
					} else {
						reportedPersonBy.setLastName("");
					}
					break;
				case 13:// ReportingPerson.firstName
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setFirstName(cell.getStringCellValue());
					} else {
						reportedPersonBy.setFirstName("");
					}
					break;
				case 14:// ReportingPerson.middleName
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setMiddleName(cell.getStringCellValue());
					} else {
						reportedPersonBy.setMiddleName("");
					}
					break;
				case 15: // ReportingPerson.relation
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setRelation(cell.getStringCellValue());
					} else {
						reportedPersonBy.setRelation("");
					}
					break;
				case 16: // ReportingPerson.telephone
					if (null != cell) {
						if (1 == cell.getCellType()) {
							reportedPersonBy.setTelephone(cell.getStringCellValue());
						}
						if (0 == cell.getCellType()) {
							reportedPersonBy.setTelephone(String.valueOf(cell.getNumericCellValue()));
						}

					} else {
						reportedPersonBy.setTelephone("");
					}
					break;
				case 17: // ReportingPerson.email
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setEmail(cell.getStringCellValue());
					} else {
						reportedPersonBy.setEmail("");
					}
					break;
				case 18: // ReportingPerson.streetAddress
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setStreetAddress(cell.getStringCellValue());
					} else {
						reportedPersonBy.setStreetAddress("");
					}
					break;
				case 19: // ReportingPerson.city
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setCity(cell.getStringCellValue());
					} else {
						reportedPersonBy.setCity("");
					}
					break;
				case 20: // ReportingPerson.state
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setState(cell.getStringCellValue());
					} else {
						reportedPersonBy.setState("");
					}
					break;
				case 21: // ReportingPerson.zip
					if (null != cell) {
						// 类型是数字
						if (0 == cell.getCellType()) {
							String zip = String.valueOf(cell.getNumericCellValue());
							reportedPersonBy.setZip(zip.substring(0, zip.indexOf(".")));
						}
						// 类型是字符串
						if (1 == cell.getCellType()) {
							reportedPersonBy.setZip(cell.getStringCellValue());
						}

					} else {
						reportedPersonBy.setZip("");
					}
					break;
				case 22: // ReportingPerson.country
					if (null != cell && 1 == cell.getCellType()) {
						reportedPersonBy.setCountry(cell.getStringCellValue());
					} else {
						reportedPersonBy.setCountry("");
					}
					break;
				case 23:// caseInfo.caseStart
					if (null != cell) {

						try {
							if (1 == cell.getCellType()) {
								summary = "Date Abuse Occurred: "+ cell.getStringCellValue();
								
								break;
							}
							
							caseInfo.setCaseStart(cell.getDateCellValue());
						} catch (Exception e) {
							exceptionLog
									.error("ETL->" + "row " + i + ", col 23, caseInfo.caseStart is not date foramt-->"
											+ cell.getStringCellValue() + "  " + e.toString());
						}
					} 
					break;
				case 24: // CaseFollowUp.caseCategory //todo numerica cell
					if (null != cell && 0 == cell.getCellType()) {
						if (0 == cell.getCellType()) {
							int j = (int)cell.getNumericCellValue();
							String s5= String.valueOf(j);
							caseFollowUp.setCaseCategory(s5);
						}
						if (1 == cell.getCellType()) {
							caseFollowUp.setCaseCategory(cell.getStringCellValue());
						}

					} else {
						caseFollowUp.setCaseCategory("");
					}
					break;
				case 25:// caseInfo.summary
					if (null != cell && 1 == cell.getCellType()) {
						if(null!=summary){
							summary = summary +" <br>"+ cell.getStringCellValue();
						}else{
							summary = cell.getStringCellValue();
						}
						
						caseInfo.setSummary(summary);
					} else {
						caseInfo.setSummary("");
					}
					break;
				case 26: // CaseInfo.typeIds electroconvulsive_therap id=1
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("1,");
						}
					}
					break;
				case 27: // CaseInfo.typeIds CASE_INCLUDED_PSYCHIATRIC_DRUGS_YN
							// id=5
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("5,");
						}

					}
					break;
				case 28: // CaseInfo.typeIds involuntary_commitment id=9
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("9,");
						}

					}
					break;
				case 29: // CASE_INCLUDED_SEXUAL_ASSAULT_YN CaseInfo.typeIds
							// id=13

					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("13,");
						}
					}
					break;
				case 30: // seclusion id=17 seclusion

					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("17,");
						}
					}
					break;
				case 31: // whistleblower id=2 whistleblower

					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("2,");
						}
					}
					break;
				case 32: // CaseInfo.typeIds DEATH id=6 death_
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("6,");
						}

					}
					break;
				case 33: // CaseInfo.typeIds id=10 _restraints
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("10,");
						}

					}
					break;
				case 34: // CaseInfo.typeIds id=14 case_type_custody
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("14,");
						}

					}
					break;
				case 35: // CaseInfo.typeIds id=18 _no_informed_consent
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("18,");
						}

					}
					break;
				case 36: // CaseInfo.typeIds id=3
							// case_type_insurance_fraud_(frd)_
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("3,");
						}

					}
					break;
				case 37: // CaseInfo.typeIds id=7 misdiagnosis
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("7,");
						}

					}
					break;
				case 38: // CaseInfo.typeIds id=11 child_drugging
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("11,");
						}

					}
					break;
				case 39: // CaseInfo.typeIds id=15 elderly_abuse
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("15,");
						}

					}
					break;
				case 40: // CaseInfo.typeIds id=19 denied_patients_rights
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("19,");
						}

					}
					break;
				case 41: // CaseInfo.typeIds id=4 court_ordered_psych_treatment
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("4,");
						}

					}
					break;
				case 42: // CaseInfo.typeIds id=8 child_protective_services
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("8,");
						}

					}
					break;
				case 43: // CaseInfo.typeIds id=12 physical_abuse
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("12,");
						}

					}
					break;
				case 44: // CaseInfo.typeIds id=16 _no_medical_exam
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("16,");
						}

					}
					break;
				case 45: // CaseInfo.typeIds id=20 case_type_coercion
					if (null != cell && 1 == cell.getCellType()) {
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")) {
							abuseTypeIDs.append("20,");
						}

					}
					break;
				case 46:// abuseTypeIDs
					if (null != cell && 1 == cell.getCellType()) {
						
						String s = cell.getStringCellValue().trim().toUpperCase();
						if (s.equals("Y") || s.equals("X")||0!=s.length()) {
							abuseTypeIDs.append("21,");
							
							if(null!=summary){
								summary = summary +" <br>"+"Type of abuse is other: "+cell.getStringCellValue();
							}else{
								summary = " <br>"+"Type of abuse is other:  "+ cell.getStringCellValue();
							}
							caseInfo.setSummary(summary);
						}
						
					/*	history.setCaseTypeOther(cell.getStringCellValue());
					} else {
						facilityInfo.setName("");*/
					}
					break;

				// begin facility object
				case 47:// CASE_FACILITY_1_NAME FacilityInfo.name col-47
					if (null != cell && 1 == cell.getCellType()) {
						facilityInfo.setName(cell.getStringCellValue());
					} else {
						facilityInfo.setName("");
					}
					break;

				case 48:// FacilityInfo.StreetAddress col-48
					if (null != cell && 1 == cell.getCellType()) {
						facilityInfo.setStreetAddress(cell.getStringCellValue());
					} else {
						facilityInfo.setStreetAddress("");
					}
					break;
				case 49:// FacilityInfo.City col-49
					if (null != cell && 1 == cell.getCellType()) {
						facilityInfo.setCity(cell.getStringCellValue());
					} else {
						facilityInfo.setCity("");
					}
					break;
				case 50:// FacilityInfo.State col-50
					if (null != cell && 1 == cell.getCellType()) {
						facilityInfo.setState(cell.getStringCellValue());
					} else {
						facilityInfo.setState("");
					}
					break;
				case 51:// FacilityInfo.zip
					if (null != cell && 0 == cell.getCellType()) {
						if (0 == cell.getCellType()) {
							facilityInfo.setZip(String.valueOf(cell.getNumericCellValue()));
						}
						if (1 == cell.getCellType()) {
							facilityInfo.setZip(cell.getStringCellValue());
						}

					} else {
						facilityInfo.setZip("");
					}
					break;
				case 52:// FacilityInfo.setCountry
					if (null != cell && 1 == cell.getCellType()) {
						facilityInfo.setCountry(cell.getStringCellValue());
					} else {
						facilityInfo.setCountry("");
					}
					break;
				case 53:// doctorInfo.setDocType
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setDocType(cell.getStringCellValue());

					} else {
						doctorInfo.setDocType("");
					}
					break;
				case 54:// doctorInfo.setName
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setName(cell.getStringCellValue());
						doctorInfo.setLastName("");
					} else {
						doctorInfo.setName("");
						doctorInfo.setLastName("");
					}
					break;
				case 55:// doctorInfo.setStreetAddress
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setStreetAddress(cell.getStringCellValue());
					} else {
						doctorInfo.setStreetAddress("");
					}
					break;
				case 56:// doctorInfo.setCity
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setCity(cell.getStringCellValue());
					} else {
						doctorInfo.setCity("");
					}
					break;
				case 57:// doctorInfo.setState
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setState(cell.getStringCellValue());
					} else {
						doctorInfo.setState("");
					}
					break;
				case 58:// doctorInfo.setZip
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setZip(cell.getStringCellValue());
					} else {
						doctorInfo.setZip("");
					}
					break;
				case 59:// doctorInfo.setCountry
					if (null != cell && 1 == cell.getCellType()) {
						doctorInfo.setCountry(cell.getStringCellValue());
					} else {
						doctorInfo.setCountry("");
					}
					break;

				case 60:// caseInfo.drugIds
					if (null != cell && 1 == cell.getCellType()) {
						try {
							String s =getDrugsIds(cell.getStringCellValue());
							if (null != s && s.length()>0){
								caseInfo.setDrugsPrescribed("1");
								caseInfo.setDrugIds(s);
							}								
							
						} catch (Exception e) {
							exceptionLog.error(
									"ETL->" + "row " + i + ", col 60, caseInfo.drugIds has error-->" + e.toString());

							caseInfo.setDrugIds("");
						}
					} else {
						caseInfo.setDrugIds("");
					}
					break;

				case 61:// caseInfo.setStillTakeDrugs
					if (null != cell && 1 == cell.getCellType()) {
						String s3= cell.getStringCellValue();
					
						if (s3.length() > 1) {
							
							if(s3.trim().toUpperCase().equals("YES")){	s3="1";}
							if(s3.trim().toUpperCase().equals("NO")){	s3="0";}
							if(!s3.trim().toUpperCase().equals("NO") && !s3.trim().toUpperCase().equals("YES")){
								caseInfo.setStillTakeDrugs("");
							}
						}
						try {

							caseInfo.setStillTakeDrugs(s3);
						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 61, caseInfo.setStillTakeDrugs has error-->" + e.toString());
							
						}
					} else {
						caseInfo.setStillTakeDrugs("");
					}
					break;

				case 62:// attorney.setWorkWithAttorney
					if (null != cell &&  1 == cell.getCellType()) {
						if (cell.getStringCellValue().length() > 3) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 62, attorney.setWorkWithAttorney lenth >3 :" + cell.getStringCellValue());
						}
						if(cell.getStringCellValue().trim().toUpperCase().equals("YES")){
						attorney.setWorkWithAttorney("1");
						}
						if(cell.getStringCellValue().trim().toUpperCase().equals("NO")){
							attorney.setWorkWithAttorney("0");
							}
					} else {
						attorney.setWorkWithAttorney("");
					}
					break;

				case 63:// caseFollowUp.setCchrReceivedAbusedDocDate
					if (null != cell) {

						
						try {
							if (0 == cell.getCellType()) {	
								String strDate ;
								   Date d2 = cell.getDateCellValue();
								SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
								if (null != d2) {
									strDate = sdf.format(d2);
								caseFollowUp.setCchrReceivedAbusedDocDate(d2);	
								caseFollowUp.setCchrReceivedAbusedDoc("1");
								caseFollowUp.setBeenContacted("1");
								if(null!=notes){
									notes = notes +" When was the Case Received by CCHR? "+strDate+" <br>";
								}else{
									notes = " When was the Case Received by CCHR?  "+ strDate+" <br>";
								}
								
								caseFollowUp.setContactedNotes(notes);		
								}
							} 

						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 63, caseFollowUp.setCchrReceivedAbusedDocDate error-->" + e.toString());

						}
					} 
					break;

				case 64:// history.HowWasTheCaseReceived
					if (null != cell && 1 == cell.getCellType()) {
						history.setHowWasTheCaseReceived(cell.getStringCellValue());
					} else {
						history.setHowWasTheCaseReceived(null);
					}
					break;

				case 65:// attorney.assistanceAttorney
					if (null != cell && 1 == cell.getCellType()) {				
										
						String s4= cell.getStringCellValue();		
						try{
							
						if (s4.length() > 1) {
							if(s4.trim().toUpperCase().equals("YES")){	s4="1";}
							if(s4.trim().toUpperCase().equals("NO")){	s4="0";}
							if(!s4.trim().toUpperCase().equals("NO") && !s4.trim().toUpperCase().equals("YES")){
								attorney.setAssistanceAttorney("");
						}	
						}
						
						attorney.setAssistanceAttorney(s4);
						}catch(Exception e){
							exceptionLog.error("ETL->" + "row " + i + ", col 65, attorney.setAssistanceAttorney error  :" + s4);
							
						}
					
					} else {
						attorney.setAssistanceAttorney("");
					}
					break;

				case 66:// caseFollowUp.setUserInsurance
					if (null != cell ) {
						if (cell.getStringCellValue().trim().toUpperCase().equals("YES"))  {
							caseFollowUp.setUserInsurance("1");  
							//caseInfo.setUserInsurance("1");  
						} 
						if (cell.getStringCellValue().trim().toUpperCase().equals("NO"))  {
							caseFollowUp.setUserInsurance("0");
							//caseInfo.setUserInsurance("0");
						} 

					} else {
						caseFollowUp.setUserInsurance("");
						//caseInfo.setUserInsurance(null);
					}
					break;

				case 67:// caseFollowUp.setInsuranceType
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setInsuranceType(cell.getStringCellValue());
						//caseInfo.setInsuranceType(cell.getStringCellValue());
					} else {
						caseFollowUp.setInsuranceType("");
						//caseInfo.setInsuranceType(null);
					}
					break;

				case 68:// caseInfo.setInsurance1CompanyName
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setInsurance1CompanyName(cell.getStringCellValue());
						//caseInfo.setInsurance1CompanyName(cell.getStringCellValue());
					} else {
						caseFollowUp.setInsurance1CompanyName("");
						//caseInfo.setInsurance1CompanyName(null);
					}
					break;

				case 69:// caseInfo.setActionIds
					if (null != cell && 1 == cell.getCellType()) {
						if(null!=summary){
							summary = summary +" <br>"+"What Actions Are They Interested in Taking on This Case? "+ cell.getStringCellValue();
						}else{
							summary = " <br>"+"What Actions Are They Interested in Taking on This Case? "+ cell.getStringCellValue();
						}
						
						caseInfo.setSummary(summary);
					} else {
						
					}
					break;

				case 70:// caseInfo.setBestTimeToContact
					if (null != cell) {

						if (0 == cell.getCellType()) {
						caseInfo.setBestTimeToContact(String.valueOf(cell.getNumericCellValue()));
						}

						if (1 == cell.getCellType()) {
							caseInfo.setBestTimeToContact(cell.getStringCellValue());
						}
					} else {
						caseInfo.setBestTimeToContact("");
					}
					break;

				case 71:// caseInfo.setActionIds
					if (null != cell && 1 == cell.getCellType()) {
						caseInfo.setBestWayToContact(cell.getStringCellValue());

					} else {
						caseInfo.setBestWayToContact("");
					}
					break;

				case 72:
						// caseFollowUp.setBeenContacted
					if (null != cell ) {
						if (0 == cell.getCellType()) {
							caseFollowUp.setContactedDate(cell.getDateCellValue()); 
							caseFollowUp.setBeenContacted("1");
						}
						if (1 == cell.getCellType()) {
						
							if(null!=notes){
								notes = notes +" was_the_case_contacted: "+ cell.getStringCellValue()+" <br>";
							}else{
								notes = " was_the_case_contacted: "+ cell.getStringCellValue()+" <br>";
							}
							caseFollowUp.setContactedNotes(notes);
							caseFollowUp.setBeenContacted("1");
						}

					}
					break;

				case 73:// caseFollowUp.setStaffMemeberFirstName
					if (null != cell && 1 == cell.getCellType()) {
						//caseFollowUp.setStaffMemeberFirstName(cell.getStringCellValue());
						//caseFollowUp.setContactedBy(cell.getStringCellValue());
					//	caseFollowUp.setFollowedType("1");

					} else {
						//caseFollowUp.setContactedBy(null);

					}
					break;

				case 74:// caseFollowUpInfo.staff_memeber_first_name
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setStaffMemeberFirstName(cell.getStringCellValue());
						caseFollowUp.setFollowedType("0");
						caseFollowUp.setBeenContacted("1");
					} else {
						caseFollowUp.setStaffMemeberFirstName("");
					}
					break;
				case 75:// caseFollowUpInfo.setStaffMemeberLastName
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setStaffMemeberLastName(cell.getStringCellValue());
						caseFollowUp.setFollowedType("0");
						caseFollowUp.setBeenContacted("1");
					} else {
						caseFollowUp.setStaffMemeberLastName("");
					}
					break;

				case 76:// caseFollowUp.setChapter
					if (null != cell && 1 == cell.getCellType()) {
						
						if(null!=notes){
								notes = notes +" Which Chapter Was it Assigned To: " + cell.getStringCellValue()+" <br>";
							}else{
								notes = " Which Chapter Was it Assigned To: " + cell.getStringCellValue()+" <br>";
							}
							caseFollowUp.setContactedNotes(notes);
							caseFollowUp.setBeenContacted("1");
						}
					
					break;

				case 77:// history.chapterFirstName
					if (null != cell && 1 == cell.getCellType()) {
						if(null!=notes){
							notes = notes +" chapter_first_name :"+ cell.getStringCellValue()+" <br>";
							history.setChapterFirstName(cell.getStringCellValue());
						}else{
							notes = " chapter_first_name :"+ cell.getStringCellValue()+" <br>";
						}
						caseFollowUp.setContactedNotes(notes);
					} else {
						history.setChapterFirstName("");
					}
					break;

				case 78:// history.chapterLasttName
					if (null != cell && 1 == cell.getCellType()) {
						if(null == caseFollowUp.getContactedNotes() ){
							caseFollowUp.setContactedNotes(" " + cell.getStringCellValue());
						}else{
						caseFollowUp.setContactedNotes(caseFollowUp.getContactedNotes()+ " "  +cell.getStringCellValue());
						history.setChapterLastName(cell.getStringCellValue());
						}
					} else {
						history.setChapterLastName("");
					}
					break;

				case 79:// history.ChapterPhoneNumber
					if (null != cell && 1 == cell.getCellType()) {
						history.setChapterPhoneNumber(cell.getStringCellValue());
					} else {
						history.setChapterPhoneNumber("");
					}
					break;
				case 80:// history.setChapterEmail
					if (null != cell && 1 == cell.getCellType()) {
						history.setChapterEmail(cell.getStringCellValue());
					} else {
						history.setChapterEmail("");
					}
					break;
				case 81:// history.setSendToChapterDate
					if (null != cell && 1 == cell.getCellType()) {
						history.setSendToChapterDate(cell.getStringCellValue());
					} 
					break;

				case 82:// history.setDateOfAckFromChapterAsCaseReceived
					if (null != cell && 0 == cell.getCellType()) {
						try {
							history.setDateOfAckFromChapterAsCaseReceived(cell.getDateCellValue().toString());

							

						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 82, history.setDateOfAckFromChapterAsCaseReceived need to be convert-->"
									+ e.toString());
						}
					} 
					break;
				case 83:// caseFollowUp.setContactedDate
					if (null != cell) {
						try {
							if (0 == cell.getCellType()) {
								caseFollowUp.setContactedDate(cell.getDateCellValue());
							} else {
								if(null!=notes){
									notes = notes +" Date the Case Was Contacted: "+ cell.getStringCellValue()+" <br>";
									history.setChapterFirstName(cell.getStringCellValue());
								}else{
									notes = " Date the Case Was Contacted: "+ cell.getStringCellValue()+" <br>";
									
								}
								caseFollowUp.setBeenContacted("1");
								caseFollowUp.setContactedNotes(notes);
								}
						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 83, caseFollowUp.setContactedDate need to be convert-->" + e.toString());
							;
						}
					} 
					break;

				case 84:// history.wasTheCaseDocumented
					if (null != cell && 1 == cell.getCellType()) {
						history.setWasTheCaseDocumented(cell.getStringCellValue());
					} else {
						history.setWasTheCaseDocumented("");
					}
					break;
				case 85:// history.wasTheDocumentationReceivedAtCchrInt
					if (null != cell && 1 == cell.getCellType()) {
						history.setWasTheDocumentationReceivedAtCchrInt(cell.getStringCellValue());
					} else {
						history.setWasTheDocumentationReceivedAtCchrInt("");
					}
					break;
				case 86:// caseFollowUp.setReferAttorneyStatus
					if (null != cell && 1 == cell.getCellType()) {
						if(cell.getStringCellValue().trim().toUpperCase().equals("YES")){
						caseFollowUp.setReferAttorneyStatus("1");
						}
						if(cell.getStringCellValue().trim().toUpperCase().equals("NO")){
							caseFollowUp.setReferAttorneyStatus("0");
							}

					} else {
						caseFollowUp.setReferAttorneyStatus("");
					}
					break;
				case 87:// caseFollowUp.setReferAttorneyDate
					if (null != cell) {
						try {
							if (0 == cell.getCellType()) {
								caseFollowUp.setReferAttorneyDate(cell.getDateCellValue());
							} 

						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 87, caseFollowUp.setReferAttorneyDate need to be convert-->"
									+ e.toString());

						}
					}
					break;
				case 88:// caseFollowUp.setReferAttorneyAccteptDate
					if (null != cell && 0 == cell.getCellType()) {
						try {
							caseFollowUp.setReferAttorneyAccteptDate(cell.getDateCellValue());
							caseFollowUp.setReferAttorneyAccteptFlag("1");
						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 88, caseFollowUp.setReferAttorneyAccteptDate need to be convert-->"
									+ e.toString());
							caseFollowUp.setReferAttorneyAccteptDate(null);
						}
					} 
					break;
				case 89:// caseFollowUp.setReferAttorneyDeclinedDate
					if (null != cell && 0 == cell.getCellType()) {
						try {
							caseFollowUp.setReferAttorneyDeclinedDate(cell.getDateCellValue());
							caseFollowUp.setReferAttorneyDeclinedFlag("1");
						} catch (Exception e) {
							exceptionLog.error("ETL->" + "row " + i
									+ ", col 89, caseFollowUp.setReferAttorneyDeclinedDate need to be convert-->"
									+ e.toString());
							caseFollowUp.setReferAttorneyDeclinedDate(null);
						}
					} 
					break;

				case 90:// caseFollowUp.setAttorneyLastName
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setAttorneyLastName(cell.getStringCellValue());

					} else {
						caseFollowUp.setAttorneyLastName("");
					}
					break;

				case 91:// caseFollowUp.setAttorneyFirstName
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setAttorneyFirstName(cell.getStringCellValue());

					} else {
						caseFollowUp.setAttorneyFirstName("");
					}
					break;

				case 92:// caseFollowUp.setCurrentStatusOnCase
					if (null != cell && 1 == cell.getCellType()) {
						caseFollowUp.setCurrentStatusOnCase(cell.getStringCellValue());

					} else {
						caseFollowUp.setCurrentStatusOnCase("");
					}
					break;

				case 93:// TODO nothting to do final_disposition_
					if (null != cell) {

					} else {

					}
					break;

				case 94:// history.setMostRecentActionTaken
					if (null != cell && 1 == cell.getCellType()) {
						history.setMostRecentActionTaken(cell.getStringCellValue());
					} else {
						history.setMostRecentActionTaken("");
					}
					break;

				case 95:// history.dateOfLastActionTaken
					if (null != cell && 1 == cell.getCellType()) {
						history.setDateOfLastActionTaken(cell.getStringCellValue());
					} else {
						history.setDateOfLastActionTaken("");
					}
					break;

				}
			}
			
			// abuseTypeIDs.deleteCharAt(abuseTypeIDs.length());
			if (null != caseInfo) {
				Date d1 = caseFollowUp.getCchrReceivedAbusedDocDate();
				SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
				if (null != d1) {

					String strDate = sdf.format(d1);

					String caseNo = "H" + strDate.substring(0, 8) + sdf.format(new Date()).substring(8);
					caseInfo.setCaseNo(caseNo);
				} else {
					
					String caseNo1 = "H" + "200001" + sdf.format(new Date()).substring(6);
					caseInfo.setCaseNo(caseNo1);
				}
				System.out.println("Start insert records row number--> " + i  + "    caseNo: " + caseInfo.getCaseNo());
			
				caseInfo.setStatus("5");
				caseInfo.setTypeIds(abuseTypeIDs.toString());
				if (null != caseInfoService) {
					caseInfoService.insertCaseInfo(caseInfo); // insert caseInfo
																// table
					//System.out.println("caseId = " + caseId + " Insert case info successfully!");
					caseId = caseInfo.getId();
				} else {
					System.out.println("caseInfoService is null");
				}
				//System.out.println("caseId = " + caseId);

			}
			if (null != history) {
				history.setCaseId(caseId);
				historyService.insertHistory(history); // insert

				//System.out.println("caseId = " + caseId + " Insert history successfully!");

			}

			if (null != abusedPerson) {
				abusedPerson.setCaseId(caseId);
				abusedPersonService.insertAbusedPerson(abusedPerson); // insert
																		// abused_person
																		// table
				//System.out.println("caseId = " + caseId + " Insert abusedPerson successfully!");

			}

			if (null != reportedPersonBy) {
				reportedPersonBy.setCaseId(caseId);
				reportingPersonService.insertReportingPerson(reportedPersonBy); // insert
																				// reporting_person
																				// table
			//	System.out.println("caseId = " + caseId + " Insert reportedPersonBy successfully!");
			}

			if (null != facilityInfo) {
				facilityInfo.setCaseId(caseId);
				facilityInfoService.insertFacilityInfo(facilityInfo); // insert
																		// facility
																		// table
				//System.out.println("caseId = " + caseId + " Insert facilityInfo successfully!");
			}

			if (null != doctorInfo) {
				doctorInfo.setCaseId(caseId);
				doctorInfoService.insertDoctorInfo(doctorInfo); // insert
																// doctor_info
																// table
				//System.out.println("caseId = " + caseId + " Insert doctorInfo successfully!");
			}

			if (null != caseFollowUp) {
				caseFollowUp.setCaseId(caseId);
				caseFollowUpService.insertCaseFollowUp(caseFollowUp); // insert
																		// case_followup
																		// table
				//System.out.println("caseId = " + caseId + " Insert caseFollowUp successfully!");
			}
			if (null != attorney) {
				attorney.setCaseId(caseId);
				attorneyService.insertAttorney(attorney); // insert attorney
															// table
				////System.out.println("caseId = " + caseId + " Insert attorney successfully!");
			}

		}
		//input.close();
	

		System.out.println("load data completed!");
	}

}
